package com.che.software.testato.domain.dao.jdbc.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.che.software.testato.domain.dao.IIterationDAO;
import com.che.software.testato.domain.dao.jdbc.adao.AbstractDAO;
import com.che.software.testato.domain.dao.jdbc.exception.IterationCreationDAOException;
import com.che.software.testato.domain.dao.jdbc.exception.IterationSearchDAOException;
import com.che.software.testato.domain.dao.jdbc.exception.IterationUpdateDAOException;
import com.che.software.testato.domain.entity.Iteration;
import com.che.software.testato.domain.entity.Prioritization;
import com.che.software.testato.domain.entity.Script;
import com.che.software.testato.domain.entity.search.IterationSearch;
import com.che.software.testato.domain.enumeration.AssignmentStatus;
import com.che.software.testato.domain.enumeration.CriterionTypes;

/**
 * JDBC implementation of the DAO interface dedicated to the iterations
 * management.
 * 
 * @author Clement HELIOU (clement.heliou@che-software.com).
 * @copyright Che Software.
 * @license GNU General Public License.
 * @see AbstractDAO, IIterationDAO.
 * @since July, 2011.
 * 
 *        This file is part of Testato.
 * 
 *        Testato is free software: you can redistribute it and/or modify it
 *        under the terms of the GNU General Public License as published by the
 *        Free Software Foundation, either version 3 of the License, or (at your
 *        option) any later version.
 * 
 *        Testato is distributed in the hope that it will be useful, but WITHOUT
 *        ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
 *        FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License
 *        for more details.
 * 
 *        You should have received a copy of the GNU General Public License
 *        along with Testato. If not, see <http://www.gnu.org/licenses/>.
 * 
 *        Testato's logo is a creation of Arrioch
 *        (http://arrioch.deviantart.com/) and it's distributed under the terms
 *        of the Creative Commons License.
 */
@Repository("iterationDAO")
public class IterationDAO extends AbstractDAO implements IIterationDAO {

	/**
	 * Constants.
	 */
	private static final Logger LOGGER = Logger.getLogger(IterationDAO.class);

	/**
	 * Creates the next iteration (both analytical and selective) for a given
	 * prioritization.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param prioritization the given prioritization.
	 * @param scripts the scripts to use for this depth.
	 * @since July, 2011.
	 * @throws IterationCreationDAOException if an error occurs during the
	 *         creation.
	 */
	@Override
	public void createNextIteration(Prioritization prioritization, List<Script> scripts) throws IterationCreationDAOException {
		LOGGER.debug("createNextIteration(" + prioritization.getPrioritizationId() + ").");
		Connection connection = null;
		try {
			connection = getDataSource().getConnection();
			connection.setAutoCommit(false);
			getQueryRunner().update(connection, "INSERT INTO iteration(iteration_id, prioritization_id) VALUES(nextval('iteration_seq'), ?) ", new Object[] { prioritization.getPrioritizationId() });
			Integer createdIterationId = (Integer) getQueryRunner().query(connection, "SELECT MAX(iteration_id)::int AS iterationId FROM iteration ", new ScalarHandler("iterationId"));
			getQueryRunner().update(connection, "INSERT INTO iteration_assignment(iteration_assignment_id, criterion_type, iteration_id, analytical_prioritization_status) VALUES(nextval('iteration_assignment_seq'), ?, ?, ?) ", new Object[] { CriterionTypes.COST.name(), createdIterationId, AssignmentStatus.NOT_ASSIGNED.name() });
			createComparisonMatrixItem(connection, scripts, (Integer) getQueryRunner().query(connection, "SELECT MAX(iteration_assignment_id)::int AS result FROM iteration_assignment ", new ScalarHandler("result")));
			getQueryRunner().update(connection, "INSERT INTO iteration_assignment(iteration_assignment_id, criterion_type, iteration_id, analytical_prioritization_status) VALUES(nextval('iteration_assignment_seq'), ?, ?, ?) ", new Object[] { CriterionTypes.FIT.name(), createdIterationId, AssignmentStatus.NOT_ASSIGNED.name() });
			createComparisonMatrixItem(connection, scripts, (Integer) getQueryRunner().query(connection, "SELECT MAX(iteration_assignment_id)::int AS result FROM iteration_assignment ", new ScalarHandler("result")));
			getQueryRunner().update(connection, "INSERT INTO iteration_assignment(iteration_assignment_id, criterion_type, iteration_id, analytical_prioritization_status) VALUES(nextval('iteration_assignment_seq'), ?, ?, ?) ", new Object[] { CriterionTypes.RISK.name(), createdIterationId, AssignmentStatus.NOT_ASSIGNED.name() });
			createComparisonMatrixItem(connection, scripts, (Integer) getQueryRunner().query(connection, "SELECT MAX(iteration_assignment_id)::int AS result FROM iteration_assignment ", new ScalarHandler("result")));
			getQueryRunner().update(connection, "INSERT INTO iteration_assignment(iteration_assignment_id, criterion_type, iteration_id, analytical_prioritization_status) VALUES(nextval('iteration_assignment_seq'), ?, ?, ?) ", new Object[] { CriterionTypes.VALUE.name(), createdIterationId, AssignmentStatus.NOT_ASSIGNED.name() });
			createComparisonMatrixItem(connection, scripts, (Integer) getQueryRunner().query(connection, "SELECT MAX(iteration_assignment_id)::int AS result FROM iteration_assignment ", new ScalarHandler("result")));
			getQueryRunner().update(connection, "INSERT INTO iteration_assignment(iteration_assignment_id, iteration_id, selective_prioritization_status) VALUES(nextval('iteration_assignment_seq'), ?, ?) ", new Object[] { createdIterationId, AssignmentStatus.NOT_ASSIGNED.name() });
			connection.commit();
		} catch (SQLException e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				throw new IterationCreationDAOException(e1);
			}
			throw new IterationCreationDAOException(e);
		} finally {
			if (null != connection) {
				DbUtils.closeQuietly(connection);
			}
		}
	}

	/**
	 * Retrieves the current iteration to process for a given prioritization.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param prioritization the given prioritization.
	 * @return the current iteration to process id.
	 * @since July, 2011.
	 * @throws IterationSearchDAOException if an error occurs during the search.
	 */
	@Override
	public int getCurrentIterationFromPrioritization(Prioritization prioritization) throws IterationSearchDAOException {
		LOGGER.debug("getCurrentIterationFromPrioritization(" + prioritization + ").");
		Connection connection = null;
		try {
			connection = getDataSource().getConnection();
			return (Integer) getQueryRunner().query(connection, "SELECT COALESCE(MAX(iteration_id),0)::int AS iterationId FROM iteration WHERE prioritization_id = ? ",
					new ScalarHandler("iterationId"), new Object[] { prioritization.getPrioritizationId() });
		} catch (SQLException e) {
			throw new IterationSearchDAOException(e);
		} finally {
			if (null != connection) {
				DbUtils.closeQuietly(connection);
			}
		}
	}

	/**
	 * Retrieves the remaining scripts count for a given prioritization.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param prioritization the given prioritization.
	 * @return the resulting count.
	 * @since July, 2011.
	 * @throws IterationSearchDAOException if an error occurs during the search.
	 */
	@Override
	public int getRemainingScriptsCountFromPrioritization(Prioritization prioritization) throws IterationSearchDAOException {
		LOGGER.debug("getRemainingScriptsCountFromPrioritization(" + prioritization.getPrioritizationId() + ").");
		Connection connection = null;
		try {
			connection = getDataSource().getConnection();
			return (Integer) getQueryRunner().query(connection, "SELECT COALESCE(((MAX(depth)-MIN(depth))+1),0)::int AS result FROM script WHERE hierarchy_id = ? AND NOT EXISTS( SELECT iteration_assignment_id FROM comparisonMatrixItem WHERE first_script = script_id ) ", new ScalarHandler("result"), new Object[] { prioritization.getHierarchyId() });
		} catch (SQLException e) {
			throw new IterationSearchDAOException(e);
		} finally {
			if (null != connection) {
				DbUtils.closeQuietly(connection);
			}
		}
	}

	/**
	 * Retrieves the scripts count for a given iteration.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param iterationId the given iteration id.
	 * @return the resulting scripts count.
	 * @since July, 2011.
	 * @throws IterationSearchDAOException if an error occurs during a search.
	 */
	@Override
	public int getScriptsCountFromIteration(int iterationId) throws IterationSearchDAOException {
		LOGGER.debug("getScriptsCountFromIteration(" + iterationId + ").");
		Connection connection = null;
		try {
			connection = getDataSource().getConnection();
			return (Integer) getQueryRunner().query(connection, "SELECT COUNT(DISTINCT(script_id))::int AS result FROM script JOIN comparisonMatrixItem ON(first_script = script_id) JOIN iteration_assignment USING(iteration_assignment_id) WHERE iteration_id = ? ", new ScalarHandler("result"), new Object[] { iterationId });
		} catch (SQLException e) {
			throw new IterationSearchDAOException(e);
		} finally {
			if (null != connection) {
				DbUtils.closeQuietly(connection);
			}
		}
	}

	/**
	 * Checks if an iteration is completed. In other words, checks if the
	 * related comparison matrix has been prioritized.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param iterationId the given iteration id.
	 * @return true if the iteration is ended, else false.
	 * @since August, 2011.
	 * @throws IterationSearchDAOException if an error occurs during the search.
	 */
	@Override
	public boolean isIterationCompleted(int iterationId) throws IterationSearchDAOException {
		LOGGER.debug("isIterationCompleted(" + iterationId + ").");
		Connection connection = null;
		try {
			connection = getDataSource().getConnection();
			return (Boolean) getQueryRunner().query(connection, "SELECT EXISTS( SELECT iteration_assignment_id FROM iteration_assignment WHERE analytical_prioritization_status = 'ENDED' AND iteration_assignment_id = ?) AS result ", new ScalarHandler("result"), new Object[] { iterationId });
		} catch (SQLException e) {
			throw new IterationSearchDAOException(e);
		} finally {
			if (null != connection) {
				DbUtils.closeQuietly(connection);
			}
		}
	}

	/**
	 * Iteration search from a bean of criterions.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param searchBean the criterions to use for the search.
	 * @return the resulting object list.
	 * @since July, 2011.
	 * @throws IterationSearchDAOException if an error occurs during the search.
	 */
	@Override
	public List<Iteration> searchIteration(IterationSearch searchBean) throws IterationSearchDAOException {
		LOGGER.debug("searchIteration().");
		Connection connection = null;
		try {
			connection = getDataSource().getConnection();
			List<Object> params = new ArrayList<Object>();
			return getQueryRunner().query(connection, getIterationSearchQueryFromCriterion(searchBean, params), new BeanListHandler<Iteration>(Iteration.class), params.toArray());
		} catch (SQLException e) {
			throw new IterationSearchDAOException(e);
		} finally {
			if (null != connection) {
				DbUtils.closeQuietly(connection);
			}
		}
	}

	/**
	 * Updates an iteration from his object.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param iterationToUpdate the iteration object to update in the database.
	 * @since July, 2011.
	 * @throws IterationUpdateDAOException if an error occurs during the update.
	 */
	@Override
	public void updateIteration(Iteration iterationToUpdate) throws IterationUpdateDAOException {
		LOGGER.debug("updateIteration(" + iterationToUpdate.getIterationAssignmentId() + ").");
		Connection connection = null;
		try {
			connection = getDataSource().getConnection();
			List<Object> params = new ArrayList<Object>();
			getQueryRunner().update(connection, getIterationUpdateQueryFromCriterion(iterationToUpdate, params), params.toArray());
		} catch (SQLException e) {
			throw new IterationUpdateDAOException(e);
		} finally {
			if (null != connection) {
				DbUtils.closeQuietly(connection);
			}
		}
	}

	/**
	 * Creates the comparison matrix items for a given iteration assignment.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param connection the connection to use.
	 * @param scripts the scripts to use to build the matrix.
	 * @param iterationAssigmentId the given iteration assignment id.
	 * @since July, 2011.
	 * @throws SQLException if an error occurs during the database process.
	 */
	private void createComparisonMatrixItem(Connection connection, List<Script> scripts, int iterationAssigmentId) throws SQLException {
		LOGGER.debug("createComparisonMatrixItem(" + iterationAssigmentId + "," + scripts + ").");
		for (Script script : scripts) {
			for (Script otherScript : scripts) {
				getQueryRunner().update(connection, "INSERT INTO comparisonMatrixItem(iteration_assignment_id, first_script, second_script, comparison_matrix_item, value) VALUES(?, ?, ?, nextval('comparison_matrix_item_seq'), ?) ", new Object[] { iterationAssigmentId, script.getScriptId(), otherScript.getScriptId(), (script.getScriptId().equals(otherScript.getScriptId())) ? 1 : null });
			}
		}
	}

	/**
	 * Recovery of the iteration search query from criterion.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param searchBean the object containing the criterions.
	 * @param params the parameters list corresponding to the built query.
	 * @return the built query.
	 * @since July, 2011.
	 */
	private String getIterationSearchQueryFromCriterion(IterationSearch searchBean, List<Object> params) {
		LOGGER.debug("getIterationSearchQueryFromCriterion().");
		setWhereClauseEnabled(false);
		StringBuilder sBuilder = new StringBuilder("SELECT iteration_id AS iterationId, iteration_assignment_id AS iterationAssignmentId, criterion_type AS criterionStringType, selection_type AS selectionStringType, service_id AS serviceId, selective_prioritization_status AS selectiveStringStatus, analytical_prioritization_status AS analyticalStringStatus, consistency_index AS consistencyIndex, version_id AS versionId FROM iteration_assignment JOIN iteration USING(iteration_id) JOIN hierarchy_prioritization USING(prioritization_id) JOIN hierarchy_version USING(hierarchy_id) ");
		if (null != searchBean && null != searchBean.getScriptId()) {
			sBuilder.append("JOIN iteration_assignment_source_script USING(iteration_assignment_id) ");
		}
		if (null != searchBean && null != searchBean.getIterationId() && 0 != searchBean.getIterationId()) {
			sBuilder.append(getWhereClauseBegin());
			sBuilder.append("iteration_id = ? ");
			params.add(searchBean.getIterationId());
		}
		if (null != searchBean && null != searchBean.getScriptId()) {
			sBuilder.append(getWhereClauseBegin());
			sBuilder.append("script_id =  ? ");
			params.add(searchBean.getScriptId());
		}
		if (null != searchBean && null != searchBean.getIterationAssignmentId() && 0 != searchBean.getIterationAssignmentId()) {
			sBuilder.append(getWhereClauseBegin());
			sBuilder.append("iteration_assignment_id = ? ");
			params.add(searchBean.getIterationAssignmentId());
		}
		if (null != searchBean && null != searchBean.getServiceId() && 0 != searchBean.getServiceId()) {
			sBuilder.append(getWhereClauseBegin());
			sBuilder.append("service_id = ? ");
			params.add(searchBean.getServiceId());
		}
		if (null != searchBean && null != searchBean.getAnalyticalStatus()) {
			sBuilder.append(getWhereClauseBegin());
			sBuilder.append("analytical_prioritization_status = ? ");
			params.add(searchBean.getAnalyticalStatus().name());
		}
		if (null != searchBean && null != searchBean.getSelectiveStatus()) {
			sBuilder.append(getWhereClauseBegin());
			sBuilder.append("selective_prioritization_status = ? ");
			params.add(searchBean.getSelectiveStatus().name());
		}
		if (null != searchBean && null != searchBean.getAnalyticalNotEnded() && true == searchBean.getAnalyticalNotEnded()) {
			sBuilder.append(getWhereClauseBegin());
			sBuilder.append("analytical_prioritization_status != ? ");
			params.add(AssignmentStatus.ENDED.name());
		}
		if (null != searchBean && null != searchBean.getSelectiveStatusNull() && true == searchBean.getSelectiveStatusNull()) {
			sBuilder.append(getWhereClauseBegin());
			sBuilder.append("selective_prioritization_status IS NULL ");
		}
		return sBuilder.toString();
	}

	/**
	 * Recovery of the iteration update query from criterion.
	 * 
	 * @author Clement HELIOU (clement.heliou@che-software.com).
	 * @param iterationToUpdate the object containing the criterions.
	 * @param params the parameters list corresponding to the built query.
	 * @return the built query.
	 * @since July, 2011.
	 */
	private String getIterationUpdateQueryFromCriterion(Iteration iterationToUpdate, List<Object> params) {
		LOGGER.debug("getIterationUpdateQueryFromCriterion().");
		setSetClauseEnabled(false);
		StringBuilder sBuilder = new StringBuilder("UPDATE iteration_assignment ");
		if (null != iterationToUpdate && null != iterationToUpdate.getServiceId() && 0 != iterationToUpdate.getServiceId()) {
			sBuilder.append(getSetClauseBegin());
			sBuilder.append("service_id = ? ");
			params.add(iterationToUpdate.getServiceId());
		}
		if (null != iterationToUpdate && null != iterationToUpdate.getConsistencyIndex()) {
			sBuilder.append(getSetClauseBegin());
			sBuilder.append("consistency_index = ? ");
			params.add(iterationToUpdate.getConsistencyIndex());
		}
		if (null != iterationToUpdate && null != iterationToUpdate.getAnalyticalStatus()) {
			sBuilder.append(getSetClauseBegin());
			sBuilder.append("analytical_prioritization_status = ? ");
			params.add(iterationToUpdate.getAnalyticalStatus().name());
		}
		if (null != iterationToUpdate && null != iterationToUpdate.getSelectiveStatus()) {
			sBuilder.append(getSetClauseBegin());
			sBuilder.append("selective_prioritization_status = ? ");
			params.add(iterationToUpdate.getSelectiveStatus().name());
		}
		if (null != iterationToUpdate && null != iterationToUpdate.getSelectionType()) {
			sBuilder.append(getSetClauseBegin());
			sBuilder.append("selection_type = ? ");
			params.add(iterationToUpdate.getSelectionType().name());
		}
		sBuilder.append("WHERE iteration_assignment_id = ? ");
		params.add(iterationToUpdate.getIterationAssignmentId());
		return sBuilder.toString();
	}
}